package com.CommunityHub.kiana.dao;


import com.CommunityHub.kiana.po.MemberDetail;
import com.CommunityHub.kiana.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class MemberDAO {
    public List<MemberDetail> getAllMembers() throws SQLException {
        List<MemberDetail> members = new ArrayList<>();
        String sql = "SELECT * FROM Member_Detail";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                MemberDetail member = new MemberDetail();
                member.setMemberId(rs.getInt("member_id"));
                member.setRealName(rs.getString("real_name"));
                member.setGender(rs.getString("gender"));
                member.setBirthDate(rs.getDate("birth_date"));
                member.setPhone(rs.getString("phone"));
                member.setIdCard(rs.getString("id_card"));
                member.setContactName(rs.getString("contact_name"));
                member.setContactPhone(rs.getString("contact_phone"));
                member.setMemberType(rs.getString("member_type"));
                member.setRemark(rs.getString("remark"));
                members.add(member);
            }
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }

        return members;
    }

    public boolean addMember(MemberDetail member) throws SQLException {
        String sql = "INSERT INTO Member_Detail (member_id, real_name, gender, birth_date, phone, id_card, contact_name, contact_phone, member_type, remark) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, member.getMemberId());
            pstmt.setString(2, member.getRealName());
            pstmt.setString(3, member.getGender());
            pstmt.setDate(4, new Date(member.getBirthDate().getTime()));
            pstmt.setString(5, member.getPhone());
            pstmt.setString(6, member.getIdCard());
            pstmt.setString(7, member.getContactName());
            pstmt.setString(8, member.getContactPhone());
            pstmt.setString(9, member.getMemberType());
            pstmt.setString(10, member.getRemark());

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }

    public boolean updateMember(MemberDetail member) throws SQLException {
        String sql = "UPDATE Member_Detail SET real_name=?, gender=?, birth_date=?, phone=?, id_card=?, contact_name=?, contact_phone=?, member_type=?, remark=? WHERE member_id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, member.getRealName());
            pstmt.setString(2, member.getGender());
            pstmt.setDate(3, new Date(member.getBirthDate().getTime()));
            pstmt.setString(4, member.getPhone());
            pstmt.setString(5, member.getIdCard());
            pstmt.setString(6, member.getContactName());
            pstmt.setString(7, member.getContactPhone());
            pstmt.setString(8, member.getMemberType());
            pstmt.setString(9, member.getRemark());
            pstmt.setInt(10, member.getMemberId());

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }

    public boolean deleteMember(int memberId) throws SQLException {
        String sql = "DELETE FROM Member_Detail WHERE member_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, memberId);

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }

    public int getAllCountMembers() throws SQLException {
        String sql = "SELECT COUNT(*) FROM Member_Detail";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int count = 0;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            if (rs.next()) {
                return rs.getInt(1); // 获取第一列的计数结果
            }

        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }

        return 0;
    }
}